// 手动运行通知迁移
const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'aide',
  user: 'postgres',
  password: 'unidt@123',
});

async function runNotificationMigration() {
  const client = await pool.connect();
  
  try {
    await client.query('BEGIN');
    
    console.log('🚀 开始通知系统数据库迁移...');

    // 1. 创建通知设置表
    await client.query(`
      CREATE TABLE IF NOT EXISTS notification_settings (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        user_id VARCHAR(255) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        task_reminders BOOLEAN DEFAULT true,
        daily_summary BOOLEAN DEFAULT true,
        weekly_review BOOLEAN DEFAULT true,
        overdue_alerts BOOLEAN DEFAULT true,
        reminder_time TIME DEFAULT '09:00',
        sound_enabled BOOLEAN DEFAULT true,
        vibration_enabled BOOLEAN DEFAULT true,
        email_notifications BOOLEAN DEFAULT false,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        UNIQUE(user_id)
      )
    `);

    console.log('✅ 通知设置表创建成功');

    // 2. 创建通知历史表
    await client.query(`
      CREATE TABLE IF NOT EXISTS notification_history (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        user_id VARCHAR(255) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        type VARCHAR(50) NOT NULL, -- 'reminder', 'overdue', 'summary', 'deadline'
        title VARCHAR(255) NOT NULL,
        message TEXT NOT NULL,
        task_id TEXT REFERENCES tasks(id) ON DELETE SET NULL,
        sent_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        is_read BOOLEAN DEFAULT false,
        read_at TIMESTAMP WITH TIME ZONE,
        delivery_status VARCHAR(20) DEFAULT 'sent', -- 'sent', 'failed', 'pending'
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
      )
    `);

    console.log('✅ 通知历史表创建成功');

    // 3. 创建通知队列表
    await client.query(`
      CREATE TABLE IF NOT EXISTS notification_queue (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        user_id VARCHAR(255) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        type VARCHAR(50) NOT NULL,
        title VARCHAR(255) NOT NULL,
        message TEXT NOT NULL,
        task_id TEXT REFERENCES tasks(id) ON DELETE CASCADE,
        scheduled_at TIMESTAMP WITH TIME ZONE NOT NULL,
        status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'sent', 'failed'
        attempts INTEGER DEFAULT 0,
        last_attempt_at TIMESTAMP WITH TIME ZONE,
        error_message TEXT,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
      )
    `);

    console.log('✅ 通知队列表创建成功');

    // 4. 为现有用户创建默认通知设置
    const usersResult = await client.query('SELECT id, email FROM users');
    
    for (const user of usersResult.rows) {
      await client.query(`
        INSERT INTO notification_settings (
          user_id, task_reminders, daily_summary, weekly_review, 
          overdue_alerts, reminder_time, sound_enabled, vibration_enabled
        ) VALUES ($1, true, true, true, true, '09:00', true, true)
        ON CONFLICT (user_id) DO NOTHING
      `, [user.id]);
      
      console.log(`✅ 为用户 ${user.email} 创建通知设置`);
    }

    await client.query('COMMIT');
    console.log('🎉 通知系统迁移完成！');
    
  } catch (error) {
    await client.query('ROLLBACK');
    console.error('❌ 迁移失败:', error.message);
    console.error('详细错误:', error);
    throw error;
  } finally {
    client.release();
    await pool.end();
  }
}

runNotificationMigration().catch(error => {
  console.error('💥 迁移过程中发生错误:', error);
  process.exit(1);
});
